package dialect

import (
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"strings"

	"gitee.com/dingiyan/goMyAdmin/service/interfaces"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// mysql 数据库方言，实现统一接口操作的对象
type MySQL struct {
	DB *gorm.DB
}

func NewMySQL() *MySQL {
	return &MySQL{}
}

// 连接一个数据源
func (d *MySQL) ConnectDatasources(config *interfaces.DatasourcesConfig) (*gorm.DB, error) {
	if d.DB != nil {
		return d.DB, nil
	}
	if config.Database == "" {
		config.Database = "mysql"
	}
	dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v?charset=utf8mb4&parseTime=True&loc=Local", config.User, config.Password, config.Host, config.Port, config.Database)
	log.Printf("dsn: %v\r\n", dsn)
	// 首先尝试从缓存中拿
	cacheObj, ok := pools[dsn]
	if ok {
		d.DB = cacheObj
		return cacheObj, nil
	}

	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		return nil, err
	}
	pools[dsn] = db

	d.DB = db
	return db, nil
}

// 创建一个数据库
func (d *MySQL) CreateDatabase(database string, options map[string]any) error {
	if d.DB == nil {
		return fmt.Errorf("db is nil")
	}
	sql := fmt.Sprintf("CREATE DATABASE IF NOT EXISTS %v DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;", database)
	return d.DB.Exec(sql).Error
}

// 删除一个数据库
func (d *MySQL) DropDatabase(database string) error {
	if d.DB == nil {
		return fmt.Errorf("db is nil")
	}
	sql := fmt.Sprintf("DROP DATABASE IF EXISTS %v;", database)
	return d.DB.Exec(sql).Error
}

// GetDatabases implements interfaces.DBManager.
func (d *MySQL) GetDatabases() ([]interfaces.DatabaseInfoObject, error) {
	sql := "SHOW DATABASES;"
	var list []interfaces.DatabaseInfoObject
	var origin []map[string]any
	err := d.DB.Raw(sql).Find(&origin).Error
	for _, v := range origin {
		name := v["Database"].(string)
		list = append(list, interfaces.DatabaseInfoObject{
			Name: name,
		})
	}
	return list, err
}

// 创建一个表
func (d *MySQL) CreateTable(table string, fields []interfaces.FieldOptions, args ...any) error {
	if d.DB == nil {
		return fmt.Errorf("db is nil")
	}

	sql := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %v (\r\n", table)

	for index, v := range fields {

		sql += fmt.Sprintf("%v %v", v.Name, v.Type)

		if !v.IsAutoIncrement {
			if v.IsNull {
				sql += " NULL"
			} else {
				sql += " NOT NULL"
			}
		}

		if v.IsAutoIncrement {
			sql += " AUTO_INCREMENT"
		}
		if v.IsPrimary {
			sql += " PRIMARY KEY"
		}
		if v.Default != "" {
			sql += fmt.Sprintf(" DEFAULT '%v'", v.Default)
		}

		if v.Comment != "" {
			sql += fmt.Sprintf(" COMMENT '%v'", v.Comment)
		}

		// 字段间隔符
		if index < len(fields)-1 {
			sql += ",\r\n"
		}

	}

	var engine = "InnoDB"
	var charset = "utf8mb4"
	var collate = "utf8mb4_general_ci"
	// 方言附加参数配置，mysql可设置引擎和字符集
	if len(args) > 0 && args[0] != nil {

		arg, ok := args[0].(map[string]any)
		if ok && arg != nil {
			// 断言需要第二个参数，否则断言失败时将会panic
			charset1, _ := arg["charset"].(string)
			collate1, _ := arg["collate"].(string)
			engine1, _ := arg["engine"].(string)

			log.Printf("mysql dialect addon args: charset:%v collate:%v engine:%v", charset1, collate1, engine1)

			if engine1 != "" {
				engine = engine1
			}
			if collate1 != "" {
				collate = collate1
			}
			if charset1 != "" {
				charset = charset1
			}

		} else {
			log.Println("mysql dialect args assert error")
		}

	}

	sql += fmt.Sprintf("\r\n) ENGINE=%v DEFAULT CHARSET=%v COLLATE=%v;", engine, charset, collate)

	log.Println(sql)

	return d.DB.Exec(sql).Error
}

// rename one table.
func (d *MySQL) RenameTable(oldTable string, newTable string) error {
	if d.DB == nil {
		return errors.New("db is nil")
	}
	err := d.DB.Migrator().RenameTable(oldTable, newTable)
	// err := d.DB.Exec(fmt.Sprintf("ALTER TABLE %v RENAME TO %v;", oldTable, newTable)).Error
	return err
}

func (d *MySQL) GetTables() ([]interfaces.TableDetail, error) {
	// return d.DB.Migrator().GetTables()
	const sql = `SHOW TABLE STATUS`
	var tables []interfaces.TableDetail
	var list []map[string]any
	err := d.DB.Raw(sql).Scan(&list).Error
	if err != nil {
		return nil, err
	}
	for _, v := range list {
		o := interfaces.TableDetail{}
		o.Name = v["Name"].(string)
		o.Comment = v["Comment"].(string)
		jsonData, err := json.Marshal(v)
		if err != nil {
			o.Others = ""
		} else {
			o.Others = string(jsonData)
		}
		tables = append(tables, o)
	}
	return tables, err
}

func (d *MySQL) DropTable(table string) error {
	table = strings.ToLower(table)
	if table == "mysql" || table == "information_schema" || table == "performance_schema" || table == "sys" {
		return errors.New("can not drop system table")
	}
	sql := fmt.Sprintf("DROP TABLE IF EXISTS %v;", table)
	return d.DB.Exec(sql).Error
}

func (d *MySQL) AddField(table string, field interfaces.FieldOptions, opts map[string]string) error {
	if d.DB == nil {
		return errors.New("db is nil")
	}

	// err := d.DB.Migrator().AddColumn(table, field.Name)
	var sql = "ALTER TABLE " + table + "\r\n"
	sql += " ADD COLUMN `" + field.Name + "` " + field.Type
	if field.IsPrimary {
		sql += ` PRIMARY KEY `
	}

	if field.IsAutoIncrement {
		sql += ` AUTO_INCREMENT `
	}

	if field.IsNull {
		sql += ` NULL `
	} else {
		sql += ` NOT NULL `
	}
	if field.Default != "" {
		sql += ` DEFAULT '` + field.Default + "' "
	}

	if field.Comment != "" {
		sql += ` COMMENT '` + field.Comment + "' "
	}

	if opts["afterField"] != "" {
		sql += " AFTER `" + opts["afterField"] + "`"
	}
	err := d.DB.Exec(sql).Error
	return err
}

func (d *MySQL) UpdateField(table string, oldField string, field interfaces.FieldOptions) error {
	if d.DB == nil {
		return errors.New("db is nil")
	}

	var sql = `ALTER TABLE ` + table + "\r\n"
	sql += " CHANGE COLUMN `" + oldField + "` `" + field.Name + "` " + field.Type

	if field.IsPrimary {
		sql += ` PRIMARY KEY `
	}

	if field.IsAutoIncrement {
		sql += ` AUTO_INCREMENT `
	}

	if field.IsNull {
		sql += ` NULL `
	} else {
		sql += ` NOT NULL `
	}
	if field.Default != "" {
		sql += ` DEFAULT '` + field.Default + "' "
	}

	if field.Comment != "" {
		sql += ` COMMENT '` + field.Comment + "' "
	}

	err := d.DB.Exec(sql).Error
	return err

}

func (d *MySQL) DeleteField(table string, field string) error {
	if d.DB == nil {
		return errors.New("db is nil")
	}
	err := d.DB.Migrator().DropColumn(table, field)
	return err
}

func (d *MySQL) GetFields(databaseName string, table string) ([]interfaces.FieldOptions, error) {
	var sql = `
	SELECT   
		COLUMN_NAME AS 'name' ,  
		COLUMN_TYPE AS 'type' ,  
		DATA_TYPE AS 'dataType',  
		COLUMN_KEY AS 'isPrimary',  
		IS_NULLABLE AS 'isNull',  
		COLUMN_DEFAULT AS 'default',  
		COLUMN_COMMENT AS 'comment' ,
		CASE   
        WHEN   EXTRA = 'auto_increment' THEN '是'  
        ELSE '否'  
    	END AS 'isAutoIncrement'  
	FROM   
		INFORMATION_SCHEMA.COLUMNS  
	WHERE   
		TABLE_SCHEMA = '%v'   
		AND TABLE_NAME = '%v'
	order by ordinal_position ASC
		;
	`

	var fields = []interfaces.FieldOptions{}
	var list []map[string]any

	sql = fmt.Sprintf(sql, databaseName, table)

	if err := d.DB.Raw(sql).Scan(&list).Error; err != nil {
		return nil, err
	}
	for _, v := range list {
		var field interfaces.FieldOptions

		field.Name, _ = v["name"].(string)
		field.Type, _ = v["type"].(string)
		field.Default, _ = v["default"].(string)
		field.Comment, _ = v["comment"].(string)

		if v["isNull"] == "YES" {
			field.IsNull = true
		}
		if v["isPrimary"] == "PRI" {
			field.IsPrimary = true
		}
		if v["isAutoIncrement"] == "是" {
			field.IsAutoIncrement = true
		}
		// fields[index] = field
		fields = append(fields, field)
	}
	return fields, nil
}

func (d *MySQL) ExecRawSQL(sql string) ([]map[string]any, int64, error) {
	// var a []map[string]any
	// tx := d.DB.Exec(sql)
	// tx.Scan(&a)
	// fmt.Println(a)
	// return a, tx.RowsAffected, tx.Error

	var a []map[string]any
	tx := d.DB.Raw(sql)
	tx.Scan(&a)
	fmt.Println(a)
	// fmt.Println(tx.Statement.RowsAffected)
	fmt.Println(tx.RowsAffected)

	return a, tx.RowsAffected, tx.Error
}
